package me.green.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import me.green.bean.Diary;

/**
 * 日记dao
 *
 * @author green
 */
public class DiaryDao {

    /**
     * 添加工作完成日志
     *
     * @param diary
     * @return
     */
    public boolean saveDiary(Diary diary) {
        String sql = "insert into diary(mood,add_date,abstract,detail,uid,state) values(?,?,?,?,?,'1')";
        Connection conn = null;
        try {
            conn = BaseDB.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, diary.getMood());
            ps.setTimestamp(2, diary.getAddDate());
            ps.setString(3, diary.getAbstractWork());
            ps.setString(4, diary.getDetail());
            ps.setInt(5, diary.getUid());
            int i = ps.executeUpdate();

            ps.close();
            return i > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDB.release(conn);
        }
        return false;
    }

    /**
     * 根据完成日志的ID查询对应的详细信息
     *
     * @param id
     * @return
     */
    public Diary getCompletedDiaryById(int id) {
        Diary cd = null;
        String sql = "select id,mood,add_date,abstract,detail from diary where state='1' and id=" + id;
        Connection conn = null;
        try {
            conn = BaseDB.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                cd = new Diary();
                cd.setId(rs.getInt("id"));
                cd.setMood(rs.getString("mood"));
                cd.setAddDate(rs.getTimestamp("add_date"));
                cd.setAbstractWork(rs.getString("abstract"));
                cd.setDetail(rs.getString("detail"));
            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDB.release(conn);
        }
        return cd;
    }


    private List<Diary> resultSet2Object(ResultSet rs, List<Diary> list) throws SQLException {
        while (rs.next()) {
            Diary cd = new Diary();
            cd.setId(rs.getInt("id"));
            cd.setAddDate(rs.getTimestamp("add_date"));
            cd.setAbstractWork(rs.getString("abstract"));
            cd.setMood(rs.getString("mood"));
            cd.setDetail(rs.getString("detail"));
            list.add(cd);
        }
        return list;
    }

    /**
     * 根据日期查询[完成日志]记录，返回一对象集合，若没有记录，返回一空的集合
     *
     * @param weekBefore
     * @param weekEnd
     * @return ArrayList<CompletedDiary>
     */
    public List<Diary> queryDiary(int uid, java.sql.Date weekBefore, java.sql.Date weekEnd) {
        List<Diary> list = new ArrayList<Diary>();
        String sql = "SELECT id,mood,add_date,abstract,detail " +
                " FROM diary " +
                " WHERE state='1' AND uid=? AND add_date BETWEEN ? AND ? " +
                " ORDER BY add_date DESC ";
        Connection conn = null;
        try {
            conn = BaseDB.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, uid);
            ps.setDate(2, weekBefore);
            ps.setDate(3, weekEnd);

            ResultSet rs = ps.executeQuery();
            resultSet2Object(rs, list);
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDB.release(conn);
        }

        return list;
    }

    public List<Diary> queryAllDiary(int uid) {
        LinkedList<Diary> list = new LinkedList<Diary>();
        String sql = "select id,mood,add_date,abstract,detail " +
                " from diary " +
                " where state='1' and uid=? " +
                " order by add_date desc";
        Connection conn = null;
        try {
            conn = BaseDB.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, uid);
            ResultSet rs = ps.executeQuery();
            resultSet2Object(rs, list);
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDB.release(conn);
        }
        return list;
    }

    /**
     * 根据ID修改原有的[完成日志]，返回结果为一布尔值
     *
     * @param completedDiary
     * @return
     */
    public boolean modCompletedDiaryById(Diary completedDiary) {
        String sql = "update diary set mood=?,add_date=?,abstract=?,detail=? where id=?";
        Connection conn = null;
        try {
            conn = BaseDB.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, completedDiary.getMood());
            ps.setTimestamp(2, completedDiary.getAddDate());
            ps.setString(3, completedDiary.getAbstractWork());
            ps.setString(4, completedDiary.getDetail());
            ps.setInt(5, completedDiary.getId());

            int i = ps.executeUpdate();
            ps.close();
            return i > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDB.release(conn);
        }
        return false;
    }

    /**
     * 根据ID删除完成日志，返回结果为一布尔值
     *
     * @param id
     * @return
     */
    public boolean delCompletedDiaryById(int id) {
        String sql = "update diary set state='0' where id=" + id;
        Connection conn = null;
        try {
            conn = BaseDB.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = ps.executeUpdate();
            ps.close();
            return i > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDB.release(conn);
        }
        return false;

    }
}
